﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Common.Framework.GlobalEntities;
using System.Text;
using System.Data.Objects;

namespace ProductsServer.DA
{
	public class DALookupTables : BaseDA
	{
        public LookupTableDTO GetLookupTable(string tableName)
        {
            LookupTableDTO ltDTO = new LookupTableDTO();
            using (PPEntities dbContext = new PPEntities())
            {
                LookupTable table = dbContext.LookupTable.Where(a => a.Name == tableName).FirstOrDefault();
                if (table != null)
                {
                    ltDTO.TableName = tableName;
                    if (table.Type == (int)Enums.LookupTableType.Value)
                    {                        
                        ltDTO.Values = table.LookupTableValue.ToDictionary(a => a.ValueId, a => a.ValueName);
                    }
                    else if (table.Type == (int)Enums.LookupTableType.SQL)
                    {
                        ltDTO.Values = GetSQLLookupTable(table, dbContext);    
                    }
                }
            }

            return ltDTO;
        }

        private Dictionary<int, string> GetSQLLookupTable(LookupTable table, PPEntities dbContext )
        {
            Dictionary<int, string> res = new Dictionary<int, string>();
            object[] parameters = {table.LookupTableSQL.AppTableName,
                                                  table.LookupTableSQL.IdColumnName,
                                                  table.LookupTableSQL.ValueColumnName };

            StringBuilder query = new StringBuilder(string.Format("SELECT {1} as KeyInt, {2} as ValueString " +
                "FROM dbo.{0} ", parameters));
            if (!string.IsNullOrEmpty(table.LookupTableSQL.WhereClause))
            {
                query.Append(string.Format("WHERE {0} ", table.LookupTableSQL.WhereClause));
            }
            if (!string.IsNullOrEmpty(table.LookupTableSQL.OrderByClause))
            {
                query.Append(string.Format("Order by {0} ", table.LookupTableSQL.OrderByClause));
            }

            ObjectResult<KeyValue> dbResult = dbContext.ExecuteStoreQuery<KeyValue>(query.ToString());
            foreach (var item in dbResult)
            {
                res.Add(item.KeyInt, item.ValueString);
            }

            return res;
        }

       public class KeyValue
       {
           public int KeyInt { get; set; }
           public string ValueString { get; set; }
       }

     
	}
}